package com.example.autoconfig.app.jdbc;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * PrepareStatement 支持预处理，占位符，效率高，只需要sql编译一次
 * Statement每次执行都需要sql编译
 * 每次通过DriverManager.getConnection获取数据库连接，效率低，平凡的创建销毁数据库连接需要消耗性能，暂用资源，通过数据库连接池优化
 * @author fangzhuangzhan
 * @date 2021/09/05 11:24
 */
@Controller
@RequestMapping("/jdbc")
public class JDCBController {

    @GetMapping("/save")
    public String get(){
        save();
        return "success";
    }
    @GetMapping("/delete")
    public String del(String name){
        delete(name);
        return "success";
    }

    @GetMapping("/queryAll")
    public String query(){
        getAll();
        return "success";
    }

    @GetMapping("/update")
    public String up(String name,@RequestParam(defaultValue = "99") Integer age){
        Student student = new Student();
        student.setAge(age);
        student.setName(name);
        update(student);
        return "success";
    }


    public void save() {
        try {
            String sql = "insert into student(name,age) values(?,?);";
            Connection con = null;
            PreparedStatement stmt = null;
            // 连接对象
            con = HikariCPUti.getMasterConnection();
            // 执行命令对象
            stmt =  con.prepareStatement(sql);
            stmt.setString(1,"hadeng");
            stmt.setInt(2,33);
            // 执行
            stmt.execute();
            // 关闭
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void delete(String name) {
        try {
            String sql = "delete from student where name=?";
            Connection con = null;
            PreparedStatement stmt = null;
            // 连接对象
            con = HikariCPUti.getMasterConnection();
            // 执行命令对象
            stmt =  con.prepareStatement(sql);
            // 执行
            stmt.setString(1,name);
            stmt.execute();
            // 关闭
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static int update(Student student) {
        Connection con = null;
        PreparedStatement statement = null;
        int i = 0;
        String sql = "update student set age=? where name=?";
        try {
            con = HikariCPUti.getMasterConnection();
            statement = con.prepareStatement(sql);
            statement.setInt(1,student.getAge());
            statement.setString(2, student.getName());
            statement.execute();
            statement.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;
    }

    private static Integer getAll() {
        Connection conn = null;
        String sql = "select * from student";
        PreparedStatement pstmt = null;
        try {

            conn = HikariCPUti.getSlaveConnection();

            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery(sql);
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("============================");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @GetMapping("insertMillion")
    public Object insertMillionData(){

        // 使用preparedStatement插入100W订单
        try {
            final long start = System.currentTimeMillis();
            System.out.println("insert start : " + start );
            // insert into order(id,orderNo, clientId, desc, address, createTime, updateTime,state) values(1,"1","2","3","4",123123,123,"56");
            String sql = "insert into order(`id`, `orderNo`, `clientId`, `desc`, `address`, `createTime`, `updateTime`, `state`) values(?,?,?,?,?,?,?,?);";
            Connection con = null;
            PreparedStatement stmt = null;
            // 连接对象
            con = HikariCPUti.getMasterConnection();
            con.setAutoCommit(false);
            // 执行命令对象
            stmt =  con.prepareStatement(sql);
            for (int i = 0; i < 1; i++) {
                stmt.setInt(1,i);
                stmt.setString(2,"orderNo" + i);
                stmt.setString(3,"clientId" + i);
                stmt.setString(4,"desc" + i);
                stmt.setString(5,"address: sichuanchengdu1111");
                stmt.setLong(6,System.currentTimeMillis());
                stmt.setLong(7,System.currentTimeMillis());
                stmt.setString(8,"init");
                stmt.addBatch();
                if(i %10000 == 0){
                    stmt.executeBatch();
                    stmt.clearBatch();
                }
            }
            con.commit();
            final long end = System.currentTimeMillis();
            System.out.println("insert end : " + end );
            final long time = (end - start) / 1000;
            System.out.println("总耗时：" + time +"s");

            // 关闭
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "success";
    }

}
